"""
Case Type   : A兼容性-type功能增强
Case Name   : 使用抽象数据类型：创建static方法，static procedure和static function
Create At   : 2025/5/22
Owner       : wan005
Description :
    1.创建抽象类型；
    2.创建抽象类型体;
    3.创建行对象表，插入数据;
    4.查询数据
    5.查询数据
    6.查询数据
Expect      :
    1.成功
    2.成功
    3.成功
    4.查询到两条数据
    5.查询到两条数据
    6.查询到两条数据
History     :
"""
import os
import unittest
from yat.test import Node
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class Security(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'---{os.path.basename(__file__)} start---')
        self.userNode = Node(node='PrimaryDbAmode')
        self.sh_primy = CommonSH()
        self.common = Common()
        self.db_name = self.userNode.db_name
        self.type = 't_032'
        self.table = 'tb_032'

    def test_security(self):
        text = '----step1:创建抽象类型;expect:成功----'
        self.logger.info(text)
        sql_cmd1 = f'create type {self.type} as object(a int,name ' \
                   f'varchar2(10),constructor function {self.type}(m_a int) ' \
                   f'return self as result);'
        res1 = self.sh_primy.execut_db_sql(sql_cmd1, dbname=self.db_name)
        self.logger.info(res1)
        self.assertEqual('CREATE TYPE', res1, '执行失败' + text)

        text = '----step2:创建抽象类型体;expect:成功----'
        self.logger.info(text)
        sql_cmd2 = f'''
        create or replace type body {self.type} as constructor function 
        {self.type}(m_a int) return self as result is
            begin
            a:=m_a;
            return;
            end;
            end;
            '''
        res2 = self.sh_primy.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.logger.info(res2)
        self.assertEqual('CREATE TYPE', res2, '执行失败' + text)

        text = '----step3:创建对象表;expect:成功----'
        self.logger.info(text)
        sql_cmd3 = f'create table {self.table} of {self.type};' \
               f'insert into {self.table} values(1,\'nanjing\');' \
               f'insert into {self.table} values(1,{self.type}(2,\'shanxi\'));'
        res3 = self.sh_primy.execut_db_sql(sql_cmd3, dbname=self.db_name)
        self.logger.info(res3)
        self.assertEqual(2, res3.count('INSERT'), '执行失败' + text)

        text = '----step4:查询数据;expect:查询到两条数据----'
        self.logger.info(text)
        sql_cmd4 = f'select name from {self.table};'
        res4 = self.sh_primy.execut_db_sql(sql_cmd4, dbname=self.db_name)
        self.logger.info(res4)
        self.common.equal_sql_mdg(res4, 'name', 'nanjing', '(2,shanxi)',
                                  '(2 rows)', flag=1)

        text = '----step5:查询数据;expect:查询到两条数据----'
        self.logger.info(text)
        sql_cmd5 = f'select a from {self.table};'
        res5 = self.sh_primy.execut_db_sql(sql_cmd5, dbname=self.db_name)
        self.logger.info(res5)
        self.common.equal_sql_mdg(res5, 'a', '1', '1', '(2 rows)', flag=1)

        text = '----step6:查询数据;expect:查询到两条数据----'
        self.logger.info(text)
        sql_cmd6 = f'select value(c) from {self.table} c;'
        res6 = self.sh_primy.execut_db_sql(sql_cmd6, dbname=self.db_name)
        self.logger.info(res6)
        self.common.equal_sql_mdg(res6, 'value', '(1,nanjing)',
                                  '(1,"(2,shanxi)")', '(2 rows)', flag=1)

    def tearDown(self):
        text = '----step1:清理环境;expect:成功----'
        cle_cmd1 = f'drop table {self.table};'
        cle_res1 = self.sh_primy.execut_db_sql(cle_cmd1, dbname=self.db_name)
        self.logger.info(cle_res1)
        self.assertEqual('DROP TABLE', cle_res1, text + '执行失败')

        cle_cmd2 = f'drop type {self.type};'
        cle_res2 = self.sh_primy.execut_db_sql(cle_cmd2, dbname=self.db_name)
        self.logger.info(cle_res2)
        self.assertEqual('DROP TYPE', cle_res2, text + '执行失败')
        self.logger.info(f'---{os.path.basename(__file__)} end---')
